package com.hp.servlet;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
	
	DBUtil dbUtil=new DBUtil();
	
	public User queryUserById(int id) {
		String sql="SELECT * FROM login WHERE id=?";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=DBUtil.getConnection().prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			while (rs.next()) {
				String name=rs.getString("name");
				String pwd=rs.getString("pwd");
				
				
				User ue=new User(id,name, pwd);
				return ue;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (rs !=null) {
					rs.close();
				}
				if (ps!=null) {
					ps.close();
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}		
		return null;
	}
	
	public List<User> queryAllUser(){
		List<User> list=new ArrayList<User>();
		String sql="SELECT * FROM login";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=DBUtil.getConnection().prepareStatement(sql);
			rs=ps.executeQuery();
			while (rs.next()) {
				int id=rs.getInt("id");
				String name=rs.getString("name");
				String pwd=rs.getString("pwd");
				
				
				User ue=new User(id,name, pwd);
				list.add(ue);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (rs !=null) {
					rs.close();
				}
				if (ps!=null) {
					ps.close();
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return list;
	}
	
	public boolean inserUser(User ue) {
		String sql="INSERT INTO login VALUES (0,?, ?, )";
		PreparedStatement ps=null;
		try {
			ps = DBUtil.getConnection().prepareStatement(sql);
			
			ps.setString(1, ue.getName());
			ps.setString(2, ue.getPwd());
			
			int row=ps.executeUpdate();
			if (row>0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			}finally {
				if (ps !=null) {
					try {
						ps.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				
			}
		return false;
	}

	public boolean updateUser(User ue) {
		// TODO Auto-generated method stub
		String sql="UPDATE login SET name=?,pwd=? WHERE id=?";
		PreparedStatement ps=null;
		try {
			ps=DBUtil.getConnection().prepareStatement(sql);
			
			ps.setString(1, ue.getName());
			ps.setString(2, ue.getPwd());
			ps.setInt(5, ue.getId());
			
			int num=ps.executeUpdate();
			if (num>0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (ps !=null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
		}
		return false;
	}

	public User deleteUserById(int id) {
		String sql="DELETE FROM login WHERE id=?";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=DBUtil.getConnection().prepareStatement(sql);
			ps.setInt(1,id);
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (rs !=null) {
					rs.close();
				}
				if (ps!=null) {
					ps.close();
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}		
		return null;
	}
}
